﻿using LandManager.Web.Models;
using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq;
using System.Web;

namespace LandManager.Web.Service
{
    public class LandService : IService<LandInfo>
    {
        public bool Add(LandInfo t)
        {
            using (DataComDataContext db = new DataComDataContext())
            {
                try
                {
                    db.LandInfo.InsertOnSubmit(t);
                    db.SubmitChanges();

                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Delete(LandInfo t)
        {
            throw new NotImplementedException();
        }

        public bool Edit(LandInfo t)
        {
            using (DataComDataContext db = new DataComDataContext())
            {
                LandInfo oneLandInfo = (from c in db.LandInfo
                                        where c.ZDBH == t.ZDBH
                                        select c).Single();

                #region 为修改的字段赋值
                oneLandInfo.DJH = t.DJH;
                oneLandInfo.land_actual_user = t.land_actual_user;
                oneLandInfo.land_agency = t.land_agency;
                oneLandInfo.land_area = t.land_area;
                oneLandInfo.land_certificate_agency = t.land_certificate_agency;
                oneLandInfo.land_certificate_Date = t.land_certificate_Date;
                oneLandInfo.land_certificate_ID = t.land_certificate_ID;
                oneLandInfo.land_certificate_others = t.land_certificate_others;
                oneLandInfo.land_certificate_reason = t.land_certificate_reason;
                oneLandInfo.land_certificate_status = t.land_certificate_status;
                oneLandInfo.land_getway = t.land_getway;
                oneLandInfo.land_Is_development = t.land_Is_development;
                oneLandInfo.land_Is_IPO = t.land_Is_IPO;
                oneLandInfo.land_Is_mortgage = t.land_Is_mortgage;
                oneLandInfo.land_is_pollution_removal = t.land_is_pollution_removal;
                oneLandInfo.land_is_removal = t.land_is_removal;
                oneLandInfo.land_Is_rent = t.land_Is_rent;
                oneLandInfo.land_is_shanty = t.land_is_shanty;
                oneLandInfo.land_is_usereqlobligee = t.land_is_usereqlobligee;
                oneLandInfo.land_location = t.land_location;
                oneLandInfo.land_mortgage_area = t.land_mortgage_area;
                oneLandInfo.land_mortgage_date = t.land_mortgage_date;
                oneLandInfo.land_mortgage_obj = t.land_mortgage_obj;
                oneLandInfo.land_ownDate = t.land_ownDate;
                oneLandInfo.land_price = t.land_price;
                oneLandInfo.land_range = t.land_range;
                oneLandInfo.land_remark = t.land_remark;
                oneLandInfo.land_rent_contract = t.land_rent_contract;
                oneLandInfo.land_rent_date = t.land_rent_date;
                oneLandInfo.land_rent_obj = t.land_rent_obj;
                oneLandInfo.land_type = t.land_type;
                oneLandInfo.land_up_desc = t.land_up_desc;
                oneLandInfo.land_user = t.land_user;
                oneLandInfo.land_isHandle = t.land_isHandle;
                #endregion

                db.SubmitChanges();

                return true;
            }
        }

        public List<LandInfo> GetList()
        {
            using (DataComDataContext db = new DataComDataContext())
            {
                DataLoadOptions options = new DataLoadOptions();
                options.LoadWith<LandInfo>(c => c.Agency);
                db.LoadOptions = options;

                //var q = db.ExecuteQuery<LandInfo>(@"select * from dbo.LandInfo").ToList();

                var q = from c in db.LandInfo
                        where c.land_isHandle == 0
                        select c;

                if (q.Count() > 0)
                {
                    return q.ToList();
                }
                else
                {
                    return null;
                }
            }
        }

        public List<LandInfo> GetList(int handle = 0)
        {
            using (DataComDataContext db = new DataComDataContext())
            {
                DataLoadOptions options = new DataLoadOptions();
                options.LoadWith<LandInfo>(c => c.Agency);
                db.LoadOptions = options;

                //var q = db.ExecuteQuery<LandInfo>(@"select * from dbo.LandInfo").ToList();

                var q = from c in db.LandInfo
                        where c.land_isHandle == handle
                        select c;

                if (q.Count() > 0)
                {
                    return q.ToList();
                }
                else
                {
                    return null;
                }
            }
        }

        public List<LandInfo> GetHandleList()
        {
            using (DataComDataContext db = new DataComDataContext())
            {
                DataLoadOptions options = new DataLoadOptions();
                options.LoadWith<LandInfo>(c => c.Agency);
                db.LoadOptions = options;

                //var q = db.ExecuteQuery<LandInfo>(@"select * from dbo.LandInfo").ToList();

                var q = from c in db.LandInfo
                        where c.land_isHandle == 1
                        select c;

                if (q.Count() > 0)
                {
                    return q.ToList();
                }
                else
                {
                    return null;
                }
            }
        }

        public LandInfo FindByID(int id)
        {
            using (DataComDataContext db = new DataComDataContext())
            {
                var q = from c in db.LandInfo
                        where c.ZDBH == id
                        select c;
                if (q.Count() > 0)
                    return q.SingleOrDefault();
                else
                    return null;
            }
        }

        public LandInfo FindByID(decimal id)
        {
            using (DataComDataContext db = new DataComDataContext())
            {
                var q = from c in db.LandInfo
                        where c.ZDBH == id
                        select c;
                if (q.Count() > 0)
                    return q.SingleOrDefault();
                else
                    return null;
            }
        }

        public List<LandInfo> QueryLandWithCondition(string condition)
        {
            string sql = "select * from dbo.LandInfo where " + condition;
            using (DataComDataContext db = new DataComDataContext())
            {
                DataLoadOptions options = new DataLoadOptions();
                options.LoadWith<LandInfo>(c => c.Agency);
                db.LoadOptions = options;

                var t = db.ExecuteQuery<LandInfo>(sql).ToList();
                return t;
            }
        }

        public List<PieDataModel> QueryLandWithSQL(string sql)
        {
            using (DataComDataContext db = new DataComDataContext())
            {

                var t = db.ExecuteQuery<PieDataModel>(sql).ToList();
                return t;
            }
        }

        public ReportModel GetReportModelWithSQL(string sql)
        {
            //sql = @"SELECT count(*) as landcount, sum(land_area) as landarea, sum(land_price) as landprice FROM [dbo].[LandInfo] WHERE land_isHandle = 0;";
            using (DataComDataContext db = new DataComDataContext())
            {
                var t = db.ExecuteQuery<ReportModel>(sql).SingleOrDefault();
                return t;
            }
        }

        public Dictionary<string, int> GetDicWithSQL(string sql)
        {

            Dictionary<string, int> dic = new Dictionary<string, int>();

            using (DataComDataContext db = new DataComDataContext())
            {
                var t = db.ExecuteQuery<TempModel>(sql).ToList();

                foreach (TempModel item in t)
                {
                    dic.Add(item.TempKey, item.TempValue);
                }
                return dic;
            }
        }

        public List<InfoModel> GetInfoMoedelWithSQL()
        {
            var sql = @"select count(ZDBH) as LandCount, b.type_name AS LandType, 
                        ISNULL(sum(land_area), 0) as LandArea, ISNULL(sum(land_price), 0) as LandPrice,
                        count(Land_agency) as AgencyCount
                        from (select * from LandInfo ) a RIGHT JOIN landtype b on a.land_type = b.type_name
                        GROUP BY b.type_name ORDER BY LandCount desc ";
            using (DataComDataContext db = new DataComDataContext())
            {
                var t = db.ExecuteQuery<InfoModel>(sql).ToList();
                return t;
            }
        }

    }

    public class PieDataModel
    {
        public int value { get; set; }
        public string name { get; set; }
    }

    public class ReportModel
    {
        public int LandCount { get; set; }

        public object LandArea { get; set; }

        public object LandPrice { get; set; }
    }

    public class TempModel
    {
        public string TempKey { get; set; }

        public int TempValue { get; set; }
    }
}